package com.youmoula.mtsq.flour

import android.annotation.SuppressLint
import android.content.Context
import android.content.res.AssetManager
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import androidx.core.database.getStringOrNull
import androidx.core.database.sqlite.SQLiteCursorCompat
import androidx.core.text.TextUtilsCompat
import com.google.gson.Gson
import com.google.gson.JsonArray
import com.google.gson.JsonElement
import com.google.gson.JsonObject
import com.youmoula.mtsq.data.Recipe
import com.youmoula.mtsq.data.RecipeList
import org.json.JSONObject
import java.io.BufferedReader
import java.io.InputStreamReader
import java.util.Objects
import kotlin.random.Random

class FlourKtInDb (context: Context, factory: SQLiteDatabase.CursorFactory?) :
    SQLiteOpenHelper(context, DATABASE_NAME, factory, DATABASE_VERSION)
{
        var TAG:String = FlourKtInDb::class.simpleName.toString()
        var contextIntern = context
    override fun onCreate(db: SQLiteDatabase) {
        // below is a sqlite query, where column names
        // along with their data types is given
        val schemaSql = importSql("schema.sql")
        // we are calling sqlite
        // method for executing our query
        db.execSQL(schemaSql)

        importSqlOneByOne(db,"recipe.json")

    }


    override fun onUpgrade(db: SQLiteDatabase, p1: Int, p2: Int) {
        // this method is to check if table already exists
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME)
        onCreate(db)
    }

    fun importSql(fileName: String):String {

        var assertManager:AssetManager = contextIntern.assets

        var inputStream = assertManager.open(fileName);
        var bufferedReader = BufferedReader(InputStreamReader(inputStream));
        //var line:String
        return bufferedReader.readText();

    }

    fun importSqlOneByOne(db: SQLiteDatabase, fileName: String) {

        var assertManager:AssetManager = contextIntern.assets

        var inputStream = assertManager.open(fileName);
        var bufferedReader = BufferedReader(InputStreamReader(inputStream));
        var line:String
        var gson = Gson();
        for(line in bufferedReader.lineSequence()) {
            if (line != null) {
                var xline = line.replace("[{", "{").replace("}]", "}").replace("},","}")
                var map = gson.fromJson<JsonObject>(xline, JsonObject::class.java);
                try {
                    db.execSQL(
                        "insert into `recipe` values (null, ?, ?,?,?,?,?,?,?,?,?)",
                        arrayOf(
                            map.get("guid").asString,
                            map.get("recipe_title").asString,
                            Objects.toString(map.get("recipe_image"), null),
                            Objects.toString(map.get("recipe_memo"), null),
                            Objects.toString(map.get("recipe_materials"), null),
                            Objects.toString(map.get("recipe_con"), null),
                            Objects.toString(map.get("recipe_relation"), null),
                            Objects.toString(map.get("recipe_trick"), null),
                            Objects.toString(map.get("recipe_url"), null),
                            map.get("create_at").asString
                        )
                    )
                }catch (ex: Exception){
                    Log.d(TAG, ex.message+"\n"+ex.cause)
                }
            }
        }
//        bufferedReader.useLines {
//            it.map{
//                line -> db.execSQL(line)
//            }
//        }
        bufferedReader.close();
    }

    @SuppressLint("Range")
    fun getRecipeList(key: String?): RecipeList{
        var data:Cursor = getDataList(key)
        var rl = ArrayList<Recipe>()
        while(!data.isClosed && data.moveToNext()){
            var r = Recipe(
                id = data.getLong(data.getColumnIndex("id")),
                guid = data.getString(data.getColumnIndex("guid")),
                recipeTitle = data.getStringOrNull(data.getColumnIndex("recipe_title")),
                recipeImage = data.getStringOrNull(data.getColumnIndex("recipe_image")),
                recipeMemo = (data.getStringOrNull(data.getColumnIndex("recipe_memo"))?:"").replace("null", "").replace("\\r","").replace("\\t","&nbsp;").replace("\\n", "").replace("\"","").replace("\t", ""),
                recipeMaterials = (data.getStringOrNull(data.getColumnIndex("recipe_materials"))?:"").replace("null", "").replace("\\r","").replace("\\t","").replace("\\n", "").replace("\"",""),
                recipeCon = (data.getStringOrNull(data.getColumnIndex("recipe_con"))?:"").replace("null", "").replace("\\r","").replace("\\n", "").replace("\\t","").replace("\"","").replace("\t",""),
                recipeRelation = (data.getStringOrNull(data.getColumnIndex("recipe_relation"))?:"").replace("null", "").replace("\\r","").replace("\\t","&nbsp;").replace("\\n", "").replace("\"",""),
                recipeTrick = (data.getStringOrNull(data.getColumnIndex("recipe_trick"))?:"").replace("null", "").replace("\\t","&nbsp;").replace("\\n", "").replace("\"",""),
                recipeUrl = data.getStringOrNull(data.getColumnIndex("recipe_url")),
                createAt = data.getStringOrNull(data.getColumnIndex("create_at")),
            )
            rl.add(r)
        }
        return RecipeList("any", rl.toList())
    }



    fun getDataList(key: String?): Cursor {

        // here we are creating a readable
        // variable of our database
        // as we want to read value from it
        val db = this.readableDatabase
        // below code returns a cursor to
        // read data from the database
        var sqlStr:String
        var arg:Array<String>? = null
        if(key.isNullOrBlank()) {
            sqlStr = "SELECT * FROM $TABLE_NAME limit 100 offset " + Random.nextInt(
                1,
                110000
            )

        }else{
            sqlStr = "SELECT * FROM $TABLE_NAME where `recipe_title` like '%'||?||'%' limit 100 "
            arg = Array<String>(1){key}
        }
        return db.rawQuery( sqlStr
            , arg
        )
    }


    companion object{
        // here we have defined variables for our database

        // below is variable for database name
        private val DATABASE_NAME = "stick_noodle.db"

        // below is the variable for database version
        private val DATABASE_VERSION = 1

        // below is the variable for table name
        val TABLE_NAME = "recipe"

        // below is the variable for id column
        val ID_COL = "guid"

        // below is the variable for name column
        val ENTIRE_COl = "*"
    }
}
